package Codes_DAO;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.GregorianCalendar;

public class CodeValueDB implements CodeValueDAO
  {
   public CodeValueDB ()
	 {
	 }

   private Connection connect ()
     {
	  Connection con = null;
	  try
	    {
   	     Class.forName("com.mysql.jdbc.Driver");
		 String url      = "jdbc:mysql://localhost:3306/m_v_a";
		 String username = "root";
		 String password = "mysqlpswd";
		 con             = DriverManager.getConnection(url, username, password);
	    }
	  catch(ClassNotFoundException e)
	    {
		 System.err.println("Database driver not found.");
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error connecting to the database: " + e);
	    }
	  return con; 
     }

   private void disconnect (Connection con)
     {
	  try
	    {
		 con.close(); 
	    }
	  catch(SQLException e)
	    {
		 System.err.println("Error disconnecting from the database: " + e);
	    }
     }
   
   public ArrayList<CodeValue> getCodeValues ()
	 {
	  try
		{
	     Connection conn = connect();
		 ArrayList<CodeValue> cvs = new ArrayList<CodeValue>();
		 String query = "SELECT Sys, "         +
		                       "Sgmt, "        +
		                       "Cd, "          +
		                       "Val, "         +
		                       "Val2 "         +
			 			  "FROM cd_val "       +
					     "ORDER BY Sys  ASC, " +
			                      "Sgmt ASC, " +
					              "Cd   ASC";
		 PreparedStatement ps = conn.prepareStatement(query);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String Sys   = rs.getString("Sys");
		    String Sgmt  = rs.getString("Sgmt");
		    String Cd    = rs.getString("Cd");
		    String Val   = rs.getString("Val");
		    String Val2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(Sys, Sgmt, Cd, Val, Val2);
		    cvs.add(cv);
		   }
		 rs.close();
		 ps.close();
		 disconnect(conn);
		 return cvs;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }
   
   public ArrayList<CodeValue> getCodeValuesByType (String Sys, String Sgmt)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  String tmpSgmt = Sgmt.toUpperCase();
	  try
		{
	     Connection conn = connect();
		 ArrayList<CodeValue> cvs = new ArrayList<CodeValue>();
		 String query = "SELECT Sys, "         +
		                       "Sgmt, "        +
		                       "Cd, "          +
		                       "Val, "         +
		                       "Val2 "         +
			 			  "FROM cd_val "       +
			 			 "WHERE Sys  = ? "     +
			 			 "  AND Sgmt = ? "     +
					     "ORDER BY Sys  ASC, " +
			                      "Sgmt ASC, " +
					              "Cd   ASC";
		 PreparedStatement ps = conn.prepareStatement(query);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ResultSet         rs = ps.executeQuery();
		 while(rs.next())
		   {
		    String inSys   = rs.getString("Sys");
		    String inSgmt  = rs.getString("Sgmt");
		    String inCd    = rs.getString("Cd");
		    String inVal   = rs.getString("Val");
		    String inVal2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(inSys, inSgmt, inCd, inVal, inVal2);
		    cvs.add(cv);
		   }
		 rs.close();
		 ps.close();
		 disconnect(conn);
		 return cvs;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();  // for debugging
		 return null;
		}
	 }

   public CodeValue getCodeValue (String Sys, String Sgmt, String Cd)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  String tmpSgmt = Sgmt.toUpperCase();
	  String tmpCd   = Cd.toUpperCase();
	  try
		{
	     Connection conn   = connect();
	     String selCodeValue = "SELECT Sys, "     +
		                              "Sgmt, "    +
		                              "Cd, "      +
		                              "Val, "     +
		                              "Val2 "     +
				                 "FROM cd_val "   +
				                "WHERE Sys  = ? " +
				                "  AND Sgmt = ? " +
				                "  AND Cd   = ?";
	     PreparedStatement ps = conn.prepareStatement(selCodeValue);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ps.setString(3, tmpCd);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    String Val   = rs.getString("Val");
	        String Val2  = rs.getString("Val2");
		    CodeValue cv = new CodeValue(Sys, Sgmt, Cd, Val, Val2);
		    rs.close();
		    ps.close();
		    disconnect(conn);
		    return cv;
		   }
		 else
		   return null;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return null;
		}
	 }

   public boolean addCodeValue (CodeValue cv)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
	     Connection conn = connect();
		 String insert   = "INSERT "                    +
		                     "INTO cd_val (Sys, "       +
		                                  "Sgmt, "      +
		                                  "Cd, "        +
		                                  "Val, "       +
		                                  "Val2, "      +
		                                  "TMS_Updt, "  +
		                                  "User_Updt, " +
		                                  "TMS_Crtd, "  +
		                                  "User_Crtd) " +
				           "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)";
		 PreparedStatement ps = conn.prepareStatement(insert);
		 ps.setString(1, cv.getSys());
		 ps.setString(2, cv.getSgmt());
		 ps.setString(3, cv.getCd());
		 ps.setString(4, cv.getVal());
		 ps.setString(5, cv.getVal2());
		 ps.setTimestamp(6, ts);
		 ps.setString(7, "USER0001");
		 ps.setTimestamp(8, ts);
		 ps.setString(9, "USER9999");
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean deleteCodeValue (CodeValue cv)
	 {
	  try
		{
		 Connection conn = connect();
		 String delete = "DELETE "          +
		                   "FROM cd_val "   +
                          "WHERE Sys  = ? " +
                          "  AND Sgmt = ? " +
                          "  AND Cd   = ?";
		 PreparedStatement ps = conn.prepareStatement(delete);
		 ps.setString(1, cv.getSys());
		 ps.setString(2, cv.getSgmt());
		 ps.setString(3, cv.getCd());
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }

   public boolean updateCodeValue (CodeValue cv)
	 {
	  GregorianCalendar now = new GregorianCalendar();
	  Date today            = now.getTime(); 
	  long nanos            = today.getTime();
	  Timestamp ts          = new Timestamp(nanos);
	  try
		{
		 Connection conn = connect();
		 String update = "UPDATE cd_val "         +
		                    "SET Val       = ?, " +
		                        "Val2      = ?, " +
		                        "TMS_Updt  = ?, " +
					            "User_Updt = ? "  +
                          "WHERE Sys  = ? "       +
                          "  AND Sgmt = ? "       +
                          "  AND Cd   = ?";
		 PreparedStatement ps = conn.prepareStatement(update);
		 ps.setString(1, cv.getVal());
		 ps.setString(2, cv.getVal2());
		 ps.setTimestamp(3, ts);
		 ps.setString(4, "USER8888");
		 ps.setString(5, cv.getSys());
		 ps.setString(6, cv.getSgmt());
		 ps.setString(7, cv.getCd());
		 ps.executeUpdate();
		 ps.close();
	     disconnect(conn);
		 return true;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
	 	 return false;
		}
	 }

   public boolean validateCode (String Sys, String Sgmt, String Cd)
	 {
	  String tmpSys  = Sys.toUpperCase();
	  String tmpSgmt = Sgmt.toUpperCase();
	  String tmpCd   = Cd.toUpperCase();
	  try
		{
	     Connection conn   = connect();
	     String selCodeValue = "SELECT Val "      +
				                 "FROM cd_val "   +
				                "WHERE Sys  = ? " +
				                "  AND Sgmt = ? " +
				                "  AND Cd   = ?";
	     PreparedStatement ps = conn.prepareStatement(selCodeValue);
		 ps.setString(1, tmpSys);
		 ps.setString(2, tmpSgmt);
		 ps.setString(3, tmpCd);
		 ResultSet rs = ps.executeQuery();
		 if (rs.next())
		   {
		    rs.close();
		    ps.close();
		    disconnect(conn);
		    return true;
		   }
		 else
		   return false;
		}
	  catch(SQLException sqle)
		{
		 //sqle.printStackTrace();	// for debugging
		 return false;
		}
	 }


	
  }
